const { Sequelize } = require('sequelize')
const dotenv = require('dotenv')

dotenv.config({ path: '../../.env' })

// 数据库连接配置
const sequelize = new Sequelize(
  process.env.DB_NAME || 'X-OS',
  process.env.DB_USER || 'root',
  process.env.DB_PASS || 'C8AUxS70kBPsY24i',
  {
    host: process.env.DB_HOST || '47.94.202.171',
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    logging: process.env.NODE_ENV === 'development' ? console.log : false,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    },
    timezone: '+08:00',
    define: {
      timestamps: true,
      underscored: true,
      freezeTableName: true
    }
  }
)

// 导入模型
const User = require('./User')(sequelize)
const Role = require('./Role')(sequelize)
const Permission = require('./Permission')(sequelize)
const Product = require('./Product')(sequelize)
const ProductCategory = require('./ProductCategory')(sequelize)
const Customer = require('./Customer')(sequelize)
const Order = require('./Order')(sequelize)
const OrderItem = require('./OrderItem')(sequelize)
const Inventory = require('./Inventory')(sequelize)
const InventoryLog = require('./InventoryLog')(sequelize)

// 动态表格相关模型
const TableConfig = require('./TableConfig')(sequelize)
const TableColumn = require('./TableColumn')(sequelize)
const TableTheme = require('./TableTheme')(sequelize)
const TableAction = require('./TableAction')(sequelize)

// 聊天历史模型
const ChatHistory = require('./ChatHistory')(sequelize)

// 动态菜单模型
const DynamicMenu = require('./DynamicMenu')(sequelize)

// AI SQL日志模型
const AISQLLog = require('./AISQLLog')(sequelize)

// 定义关联关系
const setupAssociations = () => {
  // 用户-角色多对多关系
  User.belongsToMany(Role, { 
    through: 'user_roles',
    foreignKey: 'user_id',
    otherKey: 'role_id',
    as: 'roles'
  })
  Role.belongsToMany(User, { 
    through: 'user_roles',
    foreignKey: 'role_id',
    otherKey: 'user_id',
    as: 'users'
  })

  // 角色-权限多对多关系
  Role.belongsToMany(Permission, { 
    through: 'role_permissions',
    foreignKey: 'role_id',
    otherKey: 'permission_id',
    as: 'permissions'
  })
  Permission.belongsToMany(Role, { 
    through: 'role_permissions',
    foreignKey: 'permission_id',
    otherKey: 'role_id',
    as: 'roles'
  })

  // 产品-分类关系
  Product.belongsTo(ProductCategory, {
    foreignKey: 'category_id',
    as: 'category'
  })
  ProductCategory.hasMany(Product, {
    foreignKey: 'category_id',
    as: 'products'
  })

  // 订单-客户关系
  Order.belongsTo(Customer, {
    foreignKey: 'customer_id',
    as: 'customer'
  })
  Customer.hasMany(Order, {
    foreignKey: 'customer_id',
    as: 'orders'
  })

  // 订单-用户关系（创建者）
  Order.belongsTo(User, {
    foreignKey: 'created_by',
    as: 'creator'
  })

  // 订单-订单项关系
  Order.hasMany(OrderItem, {
    foreignKey: 'order_id',
    as: 'items'
  })
  OrderItem.belongsTo(Order, {
    foreignKey: 'order_id',
    as: 'order'
  })

  // 订单项-产品关系
  OrderItem.belongsTo(Product, {
    foreignKey: 'product_id',
    as: 'product'
  })

  // 库存-产品关系
  Inventory.belongsTo(Product, {
    foreignKey: 'product_id',
    as: 'product'
  })
  Product.hasOne(Inventory, {
    foreignKey: 'product_id',
    as: 'inventory'
  })

  // 库存日志-产品关系
  InventoryLog.belongsTo(Product, {
    foreignKey: 'product_id',
    as: 'product'
  })

  // 库存日志-用户关系（操作者）
  InventoryLog.belongsTo(User, {
    foreignKey: 'created_by',
    as: 'creator'
  })

  // 动态表格关联关系
  // 表格配置-列配置一对多关系
  TableConfig.hasMany(TableColumn, {
    foreignKey: 'table_config_id',
    as: 'columns'
  })
  TableColumn.belongsTo(TableConfig, {
    foreignKey: 'table_config_id',
    as: 'tableConfig'
  })

  // 表格配置-用户关系（创建者）
  TableConfig.belongsTo(User, {
    foreignKey: 'created_by',
    as: 'creator'
  })

  // 主题配置-用户关系（创建者）
  TableTheme.belongsTo(User, {
    foreignKey: 'created_by',
    as: 'creator'
  })

  // 操作配置-用户关系（创建者）
  TableAction.belongsTo(User, {
    foreignKey: 'created_by',
    as: 'creator'
  })

  // 聊天历史-用户关系
  ChatHistory.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'user'
  })
  User.hasMany(ChatHistory, {
    foreignKey: 'user_id',
    as: 'chatHistories'
  })

  // AI SQL日志-用户关系
  AISQLLog.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'executor'
  })
  User.hasMany(AISQLLog, {
    foreignKey: 'user_id',
    as: 'sqlLogs'
  })

  // AI SQL日志-审批用户关系
  AISQLLog.belongsTo(User, {
    foreignKey: 'approved_by',
    as: 'approver'
  })
  User.hasMany(AISQLLog, {
    foreignKey: 'approved_by',
    as: 'approvedSqlLogs'
  })
}

setupAssociations()

module.exports = {
  sequelize,
  User,
  Role,
  Permission,
  Product,
  ProductCategory,
  Customer,
  Order,
  OrderItem,
  Inventory,
  InventoryLog,
  // 动态表格模型
  TableConfig,
  TableColumn,
  TableTheme,
  TableAction,
  // 聊天历史模型
  ChatHistory,
  // 动态菜单模型
  DynamicMenu,
  // AI SQL日志模型
  AISQLLog
}